PL/SQL 基础---数据交互

PL/SQL 基础—数据交互

主要讨论PL/SQL中数据任何在PL/SQL引擎和SQL引擎之间进行交互；

一、基本数据类型

包括NUMBER、VARCHAR2、BOOLEAN、CHAR、DATE，Record等，这些数据类型在SQL和PL/SQL中可以直接转换，并且一一对应；

使用方式

    --获取数据
    SELECT ... INTO v1,v2...

    --写入数据
    INSERT INTO TABLE  VALUES(v1,v2);
    UPDATE TABLE set COLUMN = v1 WHERE C=v2; 

--获取数据
DECLARE
  v_employee_id  NUMBER(6);
  v_last_name    VARCHAR(30);

BEGIN
  SELECT e.EMPLOYEE_ID, e.LAST_NAME
    INTO  v_employee_id,v_last_name
  FROM EMPLOYEES e
  WHERE e.EMPLOYEE_ID = 100;

  DBMS_OUTPUT.put_line(v_employee_id);
  DBMS_OUTPUT.put_line(v_last_name);
END;

--写入数据
--写入
DECLARE
   v_employee_id  NUMBER(6);
   v_last_name    VARCHAR(30);

BEGIN
  v_employee_id := 100;
  v_last_name   := 'NEW NAME';

  UPDATE EMPLOYEES SET LAST_NAME = v_last_name 
  WHERE EMPLOYEE_ID = v_employee_id;

END;

--SELECT * FROM EMPLOYEES e WHERE e.EMPLOYEE_ID = 100;

二、复杂数据类型(集合)

包括TABLE, VARRAY等集合类型，这些数据类型需要程序员逐条处理或者使用Oracle提供的批量处理的关键字来完成(BULK COLLECT 和 FORALL（遍历数组）)；

使用方式

--获取数据 BULK INTO 写入数组

DECLARE 
     TYPE T_EMP_TABLE IS TABLE OF EMPLOYEES%ROWTYPE;
     TYPE T_NAME_ARRAY IS VARRAY(100) OF VARCHAR(30);
     v_emp  T_EMP_TABLE;
     v_name T_NAME_ARRAY;
BEGIN

  --BULK COLLECT INTO TABLE
  SELECT * 
  --RETURNING col1,col2 选取指定字段
  BULK COLLECT INTO v_emp
  FROM EMPLOYEES e;
  FOR i IN 1..v_emp.COUNT LOOP
      DBMS_OUTPUT.put_line(v_emp(i).LAST_NAME);
  END LOOP;

  --BULK COLLECT INTO VARRAY
  SELECT e.LAST_NAME
  BULK COLLECT INTO v_name
  FROM EMPLOYEES e;
  FOR i IN 1..v_name.COUNT LOOP
      DBMS_OUTPUT.put_line(v_name(i));
  END LOOP;

END; 

--写入数据 FORALL遍历
DECLARE
       TYPE T_EMP_TABLE IS TABLE OF EMPLOYEES%ROWTYPE;
       v_emp  T_EMP_TABLE;
BEGIN
       --BULK COLLECT INTO TABLE
      SELECT * 
      BULK COLLECT INTO v_emp
      FROM EMPLOYEES e;
      FOR i IN 1..v_emp.COUNT LOOP
          v_emp(i).LAST_NAME := 'RENAME';
      END LOOP;

      FORALL i IN 1..v_emp.COUNT 
             UPDATE EMPLOYEES e SET e.LAST_NAME = v_emp(i).LAST_NAME WHERE v_emp(i).EMPLOYEE_ID = e.EMPLOYEE_ID;

END;


--SELECT * FROM EMPLOYEES e;

隐私游标 和 显式游标

游标是一个私有的SQL工作区域， Oracle数据库中有两种游标，分别是隐式游标和显式游标，隐式游标不易被用户和程序员察觉和意识到，实际上Oracle服务器使用隐式游标来解析和执行我们提交的SQL语句； 而显式游标是程序员在程序中显式声明的；通常我们说的游标均指显式游标。

隐式游标 – SQL
在使用DML语句（INSERT, UPDATE, or DELETE）或者SELECT INTO 返回单行记录时，Oracle会隐式的完成游标的创建、开启、关闭等操作 ；

显式游标 – 自定义游标

显式游标不对应DML语句，只对应SELECT语句查询结果，且需要提前声明所对应的SELECT语句；

显式游标声明

CURSOR cursor_name [ ( [ parameter [, parameter ...] ) ]
   [ RETURN return_specification ]
   IS SELECT_statement
       [FOR UPDATE [OF [column list]];

示例

CREATE OR REPLACE FUNCTION jealousy_level (
    NAME_IN   IN   friends.NAME%TYPE) RETURN NUMBER
AS
    CURSOR jealousy_cur
    IS
       SELECT location FROM friends
        WHERE NAME = UPPER (NAME_IN);

    jealousy_rec   jealousy_cur%ROWTYPE;
   retval         NUMBER;
BEGIN
   OPEN jealousy_cur;

   FETCH jealousy_cur INTO jealousy_rec;

   IF jealousy_cur%FOUND
   THEN
      IF jealousy_rec.location = 'PUERTO RICO'
         THEN retval := 10;
      ELSIF jealousy_rec.location = 'CHICAGO'
         THEN retval := 1;
      END IF;
   END IF;

   CLOSE jealousy_cur;

   RETURN level_out;
END;

游标操作

参数名	作用
OPEN	打开游标，之后才能FETCH获取记录
FETCH cursor INTO v1,v2	获取数据，保存到变量中
%ATTRIBUTE	获取游标参数值（状态）
CLOSE	关闭游标

游标参数

参数名	作用
%FOUND	FETCH有结果为TRUE, 否则为FALSE
%NOTFOUND	FETCH无结果为TRUE, 否则为FALSE
%ROWCOUNT	结果集记录数
%ISOPEN	已执行OPEN为TRUE, 否则为FALSE

游标概念的描述（转）
游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。
游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。
游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT…INTO…查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。

游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。